set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode = 200;
set hive.exec.max.dynamic.partitions = 200;
--重复统计
with  tab_detail_repeat as (
    select
        waybill_no
         ,receive_network_code  as network_code    --接受网点
         ,receive_network_name  as network_name
         ,if(order_source_code is null or trim(order_source_code)='','其他',order_source_code) as order_source_code      --订单来源编码
         ,if(order_source_name is null or trim(order_source_name)='','其他',order_source_name) as order_source_name       --订单来源名称
         ,scan_network_type_name   --扫描网点类型
         ,proble_type_name         --操作类型名称 派件、分拨
         ,proble_type_id           --操作类型编码 4,2
         ,date(create_time) as create_date              --登记日期 yyy-MM-dd
         ,in_out_bound             --进出港类型
         ,second_level_type_code   --问题件二级类型编码 66a
         ,second_level_type_name   --问题件二级类型名称
         ,proble_type_subject_code --问题件一级类型编码 66
         ,proble_type_subject_name --问题件一级类型名称
         ,'1' as select_type       --查询类型（1接受网点 2 登记网点类型）
         ,'2' as is_repeat
    from jms_dwd.dwd_sqs_registration_problem_piece_base_dt
    where dt between date_add('{{ execution_date | cst_ds }}',-2)  and '{{ execution_date | cst_ds }}'
      and receive_network_code is not null
      and date(create_time) is not null
    union ALL
    select
        waybill_no
         ,registration_network_code as network_code--登记网点
         ,registration_network_name as network_name
         ,if(order_source_code is null or trim(order_source_code)='','其他',order_source_code) as order_source_code       --订单来源编码
         ,if(order_source_name is null or trim(order_source_name)='','其他',order_source_name) as order_source_name     --订单来源名称
         ,scan_network_type_name   --扫描网点类型
         ,proble_type_name         --操作类型名称 派件、分拨
         ,proble_type_id           --操作类型编码 4,2
         ,date(create_time) as create_date     --登记日期 yyy-MM-dd
         ,in_out_bound             --进出港类型
         ,second_level_type_code   --问题件二级类型编码 66a
         ,second_level_type_name   --问题件二级类型名称
         ,proble_type_subject_code --问题件一级类型编码 66
         ,proble_type_subject_name --问题件一级类型名称
         ,'2' as select_type       --查询类型（1接受网点 2 登记网点类型）
         ,'2' as is_repeat
    from jms_dwd.dwd_sqs_registration_problem_piece_base_dt
    where dt between date_add('{{ execution_date | cst_ds }}',-2)  and '{{ execution_date | cst_ds }}'
      and registration_network_code is not null
      and date(create_time) is not null
),
--明细去重
tab_detail_norepeat_0 as (
    select
        waybill_no
         ,receive_network_code
         ,receive_network_name
         ,registration_network_code
         ,registration_network_name
         ,order_source_code
         ,order_source_name
         ,scan_network_type_name   --扫描网点类型
         ,proble_type_name         --操作类型名称 派件、分拨
         ,proble_type_id           --操作类型编码 4,2
         ,create_date              --登记日期 yyy-MM-dd
         ,in_out_bound             --进出港类型
         ,second_level_type_code   --问题件二级类型编码 66a
         ,second_level_type_name   --问题件二级类型名称
         ,proble_type_subject_code --问题件一级类型编码 66
         ,proble_type_subject_name --问题件一级类型名称
         ,'1' as is_repeat         --是否重复 1不重复 2重复
    from
        (
            select
                waybill_no
                 ,receive_network_code
                 ,receive_network_name
                 ,registration_network_code
                 ,registration_network_name
                 ,if(order_source_code is null or trim(order_source_code)='','其他',order_source_code) as order_source_code --订单来源编码
                 ,if(order_source_name is null or trim(order_source_name)='','其他',order_source_name) as order_source_name --订单来源名称
                 ,scan_network_type_name   --扫描网点类型
                 ,proble_type_name         --操作类型名称 派件、分拨
                 ,proble_type_id           --操作类型编码 4,2
                 ,date(create_time) as create_date              --登记日期 yyy-MM-dd
                 ,in_out_bound             --进出港类型
                 ,second_level_type_code   --问题件二级类型编码 66a
                 ,second_level_type_name   --问题件二级类型名称
                 ,proble_type_subject_code --问题件一级类型编码 66
                 ,proble_type_subject_name --问题件一级类型名称
                 ,row_number() over(partition by waybill_no,second_level_type_code,receive_network_code,registration_network_code,date(create_time) order by update_time desc) as  rk
            from jms_dwd.dwd_sqs_registration_problem_piece_base_dt
            where dt between date_add('{{ execution_date | cst_ds }}',-2)  and '{{ execution_date | cst_ds }}'
              and date(create_time) between date_add('{{ execution_date | cst_ds }}',-2)  and '{{ execution_date | cst_ds }}'
             and create_time is not null
    )t where rk=1
),
--不重复明细
tab_detail_norepeat_1 as (
select
    waybill_no
   ,receive_network_code  as network_code    --网点编码
   ,receive_network_name  as network_name    --网点名称
   ,order_source_code        --订单来源编码
   ,order_source_name        --订单来源名称
   ,scan_network_type_name   --扫描网点类型
   ,proble_type_name         --操作类型名称 派件、分拨
   ,proble_type_id           --操作类型编码 4,2
   ,create_date              --登记日期 yyy-MM-dd
   ,in_out_bound             --进出港类型
   ,second_level_type_code   --问题件二级类型编码 66a
   ,second_level_type_name   --问题件二级类型名称
   ,proble_type_subject_code --问题件一级类型编码 66
   ,proble_type_subject_name --问题件一级类型名称
   ,'1' as select_type       --查询类型（1接受网点 2 登记网点类型）
   ,is_repeat                --是否有重复1不重复2重复
from tab_detail_norepeat_0
where receive_network_code is not null
union ALL
select
    waybill_no   --运单号
    ,registration_network_code as network_code --网点编码
    ,registration_network_name as network_name --网点名称
    ,order_source_code        --订单来源编码
    ,order_source_name        --订单来源名称
    ,scan_network_type_name   --扫描网点类型
    ,proble_type_name         --操作类型名称 派件、分拨
    ,proble_type_id           --操作类型编码 4,2
    ,create_date              --登记日期 yyy-MM-dd
    ,in_out_bound             --进出港类型
    ,second_level_type_code   --问题件二级类型编码 66a
    ,second_level_type_name   --问题件二级类型名称
    ,proble_type_subject_code --问题件一级类型编码 66
    ,proble_type_subject_name --问题件一级类型名称
    ,'2' as select_type       --查询类型（1接受网点 2 登记网点类型）
    ,is_repeat                --是否有重复1不重复2重复
from tab_detail_norepeat_0
where registration_network_code is not null
),
--统计
tab_cnt_0 as (
--聚合
select    --重复单号统计
    count(waybill_no)      as proble_waybill_cnt  --件量
    ,network_code                                  --网点
    ,max(network_name)      as network_name
    ,order_source_code
    ,max(order_source_name) as order_source_name
    ,max(proble_type_name)  as proble_type_name                 --操作类型名称 派件、分拨
    ,proble_type_id                                            --操作类型编码 4,2
    ,create_date                                               --登记日期 yyy-MM-dd
    ,in_out_bound                                              --进出港类型
    ,second_level_type_code                                    --问题件二级类型编码 66a
    ,max(second_level_type_name)  as second_level_type_name    --问题件二级类型名称
    ,proble_type_subject_code                                  --问题件一级类型编码 66
    ,max(proble_type_subject_name) as  proble_type_subject_name--问题件一级类型名称
    ,select_type                                               --查询类型（1接受网点 2 登记网点类型）
    ,is_repeat                                                 --是否重复（1不重复2重复）
from tab_detail_repeat
group by
    network_code --登记网点
    ,order_source_code
    ,proble_type_id           --操作类型编码 4,2
    ,create_date              --登记日期 yyy-MM-dd
    ,in_out_bound             --进出港类型
    ,second_level_type_code   --问题件二级类型编码 66a
    ,proble_type_subject_code --问题件一级类型编码 66
    ,select_type       --查询类型（1接受网点 2 登记网点类型）
    ,is_repeat

union all

select  --不重复单号统计
    count(waybill_no)      as proble_waybill_cnt  --件量
    ,network_code --网点
    ,max(network_name)      as network_name
    ,order_source_code
    ,max(order_source_name) as order_source_name
    ,max(proble_type_name)  as proble_type_name                 --操作类型名称 派件、分拨
    ,proble_type_id                                            --操作类型编码 4,2
    ,create_date                                               --登记日期 yyy-MM-dd
    ,in_out_bound                                              --进出港类型
    ,second_level_type_code                                    --问题件二级类型编码 66a
    ,max(second_level_type_name)  as second_level_type_name    --问题件二级类型名称
    ,proble_type_subject_code                                  --问题件一级类型编码 66
    ,max(proble_type_subject_name) as  proble_type_subject_name--问题件一级类型名称
    ,select_type                                               --查询类型（1接受网点 2 登记网点类型）
    ,is_repeat                                                 --是否重复（1不重复2重复）
from tab_detail_norepeat_1
group by
    network_code --登记网点
    ,order_source_code
    ,proble_type_id           --操作类型编码 4,2
    ,create_date              --登记日期 yyy-MM-dd
    ,in_out_bound             --进出港类型
    ,second_level_type_code   --问题件二级类型编码 66a
    ,proble_type_subject_code --问题件一级类型编码 66
    ,select_type       --查询类型（1接受网点 2 登记网点类型）
    ,is_repeat
),
---中转货量
center_cnt as ( --回刷3天
select
     date(scan_time) scan_time
    , scan_site_code
    , max(scan_site_name) as scan_site_name
    , max(order_source_name) as order_source_name
    , nvl(order_source_code, '其他') as order_source_code
    , sum(oper_cnt) oper_cnt
from jms_dm.dm_center_transfer_summary_new_dt--小时任务 分区是 yyyy-MM-dd ,每个分区都包含最近三天的数据
where  dt between date_add('{{ execution_date | cst_ds }}', -2) and '{{ execution_date | cst_ds }}'
    and date(scan_time) between date_add('{{ execution_date | cst_ds }}', -2) and '{{ execution_date | cst_ds }}'
    and sum_flag_code='zrr'
group by date (scan_time), scan_site_code, nvl(order_source_code, '其他')
),
--融合t-2和t-1的问题件登记统计
detail_step_1 as (
select
    nvl(t1.network_code            ,t2.network_code)            as network_code             --网点
    ,nvl(t1.network_name            ,t2.network_name)            as network_name
    ,nvl(t1.order_source_code       ,t2.order_source_code)       as order_source_code
    ,nvl(t1.order_source_name       ,t2.order_source_name)       as order_source_name
    ,nvl(t1.proble_type_name        ,t2.proble_type_name)        as proble_type_name         --操作类型名称 派件、分拨
    ,nvl(t1.proble_type_id          ,t2.proble_type_id)          as proble_type_id           --操作类型编码 4,2
    ,nvl(t1.create_date             ,date_add(t2.create_date,1))             as create_date              --登记日期 yyy-MM-dd
    ,nvl(t1.in_out_bound            ,t2.in_out_bound)            as in_out_bound             --进出港类型
    ,nvl(t1.second_level_type_code  ,t2.second_level_type_code)  as second_level_type_code   --问题件二级类型编码 66a
    ,nvl(t1.second_level_type_name  ,t2.second_level_type_name)  as second_level_type_name   --问题件二级类型名称
    ,nvl(t1.proble_type_subject_code,t2.proble_type_subject_code)as proble_type_subject_code --问题件一级类型编码 66
    ,nvl(t1.proble_type_subject_name,t2.proble_type_subject_name)as proble_type_subject_name --问题件一级类型名称
    ,nvl(t1.select_type             ,t2.select_type)             as select_type              --查询类型（1接受网点 2 登记网点类型）
    ,nvl(t1.is_repeat               ,t2.is_repeat)               as is_repeat                --是否重复（1不重复2重复）
    ,nvl(t1.proble_waybill_cnt      ,0 )                         as proble_cnt               --当日问题件量
    ,nvl(t2.proble_waybill_cnt      ,0 )                         as last_proble_cnt          --上一日问题件量
    ,nvl(t1.create_date,date_add(t2.create_date,1))      as date_time
    ,nvl(t1.create_date,date_add(t2.create_date,1))      as dt
from ( select * from tab_cnt_0 where create_date between date_add('{{ execution_date | cst_ds }}',-1) and '{{ execution_date | cst_ds }}')t1
    full join ( select * from tab_cnt_0 where create_date between date_add('{{ execution_date | cst_ds }}',-2) and date_add('{{ execution_date | cst_ds }}',-1))t2
on t1.network_code=t2.network_code
    and t1.order_source_code=t2.order_source_code
    and t1.proble_type_id=t2.proble_type_id
    and date_add(t1.create_date,-1)=t2.create_date
    and t1.in_out_bound=t2.in_out_bound
    and t1.second_level_type_code=t2.second_level_type_code
    and t1.select_type=t2.select_type
    and t1.is_repeat=t2.is_repeat
    and t1.proble_type_subject_code=t2.proble_type_subject_code
),
--融合中转货量的值
res as (
--insert overwrite table bidefault.dm_sq_registration_problem_count partition(dt)
select
    nvl(t1.network_code            ,ct.scan_site_code)            as network_code             --网点
    ,nvl(t1.network_name            ,ct.scan_site_name)            as network_name
    ,net1.agent_code                                                                         --所属代理区
    ,net1.agent_name                                                                         --所属代理区
    ,nvl(t1.order_source_code       ,ct.order_source_code)       as order_source_code
    ,nvl(t1.order_source_name       ,ct.order_source_name)       as order_source_name
    ,t1.proble_type_name         --操作类型名称 派件、分拨
    ,t1.proble_type_id           --操作类型编码 4,2
    ,nvl(t1.create_date             ,ct.scan_time) as create_date              --登记日期 yyy-MM-dd
    ,t1.in_out_bound             --进出港类型
    ,t1.second_level_type_code   --问题件二级类型编码 66a
    ,t1.second_level_type_name   --问题件二级类型名称
    ,t1.proble_type_subject_code --问题件一级类型编码 66
    ,t1.proble_type_subject_name --问题件一级类型名称
    ,t1.select_type              --查询类型（1接受网点 2 登记网点类型）
    ,t1.is_repeat                --是否重复（1不重复2重复）
    ,nvl(t1.proble_cnt      ,0 )                         as proble_cnt               --当日问题件量
    ,nvl(t1.last_proble_cnt ,0 )                         as last_proble_cnt          --上一日问题件量
    ,net1.network_type--网点类型
    ,nvl(t1.create_date             ,ct.scan_time) as date_time
    ,nvl(ct.oper_cnt,0) as oper_cnt
    ,net1.manage_code
    ,net1.manage_name
    ,net1.fran_code
    ,net1.fran_name
    ,net1.id as network_id --网点id
    ,nvl(t1.create_date             ,ct.scan_time) as dt
from detail_step_1 t1
    full join center_cnt ct
on ct.scan_time=t1.create_date and ct.order_source_name=t1.order_source_name and t1.network_code=ct.scan_site_code
    left join jms_dim.dim_network_whole_massage net1 on nvl(t1.network_code,ct.scan_site_code)=net1.code
where (t1.create_date between date_add('{{ execution_date | cst_ds }}',-1)  and '{{ execution_date | cst_ds }}'
   or ct.scan_time between date_add('{{ execution_date | cst_ds }}',-1) and '{{ execution_date | cst_ds }}')
)
insert overwrite table jms_dm.dm_sq_registration_problem_count_dt partition(dt)
select * from res
distribute by 1